<template>
  <div class="json-to-sql-container">
    <div class="json-to-sql">
      <div class="card">
        <div class="card-header">
          <h2>JSON转SQL工具</h2>
        </div>

        <div class="upload-section">
          <div
            class="file-upload"
            @drop.prevent="handleDrop"
            @dragover.prevent="handleDragOver"
            @dragleave.prevent="handleDragLeave"
          >
            <input
              type="file"
              id="jsonFile"
              accept=".json"
              @change="handleFileChange"
              class="file-input"
            />
            <label for="jsonFile" class="upload-button" ref="uploadLabel">选择JSON文件</label>
            <div class="drag-tip">或将文件拖到此处</div>
          </div>
          <div class="upload-tip">只能上传 JSON 文件</div>
        </div>

        <!-- 预览数据表格 -->
        <div v-if="previewData.length" class="preview-section">
          <h3>数据预览（显示前20行）</h3>
          <div class="table-wrapper">
            <table class="preview-table">
              <thead>
                <tr>
                  <th v-for="(col, index) in tableColumns" :key="index">{{ col }}</th>
                </tr>
              </thead>
              <tbody>
                <tr v-for="(row, rowIndex) in previewRows" :key="rowIndex">
                  <td v-for="(col, colIndex) in tableColumns" :key="colIndex">{{ row[col] }}</td>
                </tr>
              </tbody>
            </table>
          </div>
        </div>

        <!-- 下载配置 -->
        <div v-if="tableColumns.length" class="download-section">
          <div class="config-group">
            <div class="config-item">
              <label class="config-label">表名：</label>
              <input type="text" v-model="tableName" placeholder="请输入表名" class="text-input" />
            </div>
            <div class="config-item">
              <label class="config-label">SQL类型：</label>
              <select v-model="sqlType" class="select-input" style="width: 200px">
                <option value="batch_insert">批量插入</option>
                <option value="single_insert">单行插入</option>
                <option value="update">更新语句</option>
              </select>
            </div>
            <div class="config-item">
              <label class="checkbox-wrapper">
                <input type="checkbox" v-model="generateCreateTable" />
                <span>生成建表语句</span>
              </label>
            </div>
            <div class="config-item" v-if="sqlType === 'batch_insert'">
              <label class="config-label">每组行数：</label>
              <input
                type="number"
                v-model="batchSize"
                min="1"
                class="text-input"
                style="width: 100px"
              />
            </div>
            <div class="config-item" v-if="sqlType === 'update'">
              <label class="config-label">WHERE条件字段：</label>
              <select v-model="updateKeyField" class="select-input">
                <option
                  v-for="(field, index) in sqlFields"
                  :key="index"
                  :value="field"
                  v-show="selectedColumns[index]"
                >
                  {{ field }}
                </option>
              </select>
            </div>
          </div>
          <div class="download-buttons">
            <button class="download-btn" @click="handleDownload">下载</button>
            <button class="copy-btn" @click="handleCopy">复制到剪贴板</button>
          </div>
        </div>

        <!-- 映射配置 -->
        <div v-if="tableColumns.length" class="mapping-section">
          <div class="mapping-header">
            <h3>字段映射配置</h3>
            <div class="mapping-actions">
              <button class="action-btn select" @click="selectAll">全选</button>
              <button class="action-btn unselect" @click="unselectAll">全不选</button>
            </div>
          </div>
          <div class="mapping-list">
            <div v-for="(col, index) in tableColumns" :key="index" class="mapping-item">
              <label class="checkbox-wrapper">
                <input
                  type="checkbox"
                  :checked="selectedColumns[index]"
                  @change="(e) => handleColumnSelect(e, index)"
                />
                <div class="mapping-content">
                  <div class="mapping-field">
                    <span class="label">字段名称：</span>
                    <input
                      type="text"
                      :value="jsonFields[index]"
                      readonly
                      class="text-input readonly"
                    />
                  </div>
                  <div class="mapping-arrow">→</div>
                  <div class="mapping-field">
                    <span class="label">SQL字段：</span>
                    <input
                      type="text"
                      v-model="sqlFields[index]"
                      placeholder="SQL字段名"
                      class="text-input"
                    />
                  </div>
                  <div class="mapping-field">
                    <span class="label">字段类型：</span>
                    <select v-model="fieldTypes[index]" class="select-input">
                      <option value="varchar">varchar(255)</option>
                      <option value="int">int</option>
                      <option value="bigint">bigint</option>
                      <option value="text">text</option>
                      <option value="datetime">datetime</option>
                      <option value="decimal">decimal(10,2)</option>
                    </select>
                  </div>
                </div>
              </label>
            </div>
          </div>
        </div>
      </div>
    </div>
  </div>
</template>

<script setup lang="ts">
import { ref, computed } from 'vue'
import JSONbig from 'json-bigint'
// 数据相关的响应式变量
const previewData = ref([])
const tableColumns = ref([])

// 计算属性：只显示前20行数据
const previewRows = computed(() => {
  return previewData.value.slice(0, 20)
})

// 映射相关的响应式数据
const selectedColumns = ref([])
const jsonFields = ref([])
const sqlFields = ref([])
const fieldTypes = ref([])

// 下载配置相关的响应式数据
const tableName = ref('')
const generateCreateTable = ref(true)
const batchSize = ref(500)
const sqlType = ref('batch_insert')
const updateKeyField = ref('')

const uploadLabel = ref(null)

// 初始化默认字段类型
const initializeFieldTypes = () => {
  fieldTypes.value = tableColumns.value.map((col) => {
    if (col === 'id') return 'bigint'
    if (col === 'age' || col.includes('count') || col.includes('num')) return 'int'
    if (
      col.includes('price') ||
      col.includes('amount') ||
      col.includes('money') ||
      col.includes('salary')
    )
      return 'decimal'
    if (col.includes('date') || col.includes('time')) return 'datetime'
    return 'varchar'
  })
}

const handleColumnSelect = (event, index) => {
  selectedColumns.value[index] = event.target.checked
}

const handleFileChange = async (event) => {
  const file = event.target.files[0]
  if (!file) return

  if (!file.type.includes('json')) {
    alert('只能上传 JSON 文件!')
    return
  }

  try {
    const reader = new FileReader()
    reader.onload = (e) => {
      try {
        // 添加reviver函数来处理大数字
        const jsonData = JSONbig.parse(e.target.result)

        if (Array.isArray(jsonData)) {
          previewData.value = jsonData
          if (jsonData.length > 0) {
            // 获取所有数据中的所有字段
            const allFields = new Set()
            jsonData.forEach((item) => {
              Object.keys(item).forEach((key) => allFields.add(key))
            })
            tableColumns.value = Array.from(allFields)

            // 初始化映射数据
            selectedColumns.value = new Array(tableColumns.value.length).fill(true)
            jsonFields.value = [...tableColumns.value]
            sqlFields.value = [...tableColumns.value]

            // 设置默认表名
            tableName.value = file.name.replace('.json', '')

            // 初始化字段类型
            initializeFieldTypes()

            // 设置默认的更新字段（如果存在id字段）
            const idIndex = tableColumns.value.findIndex((col) => col.toLowerCase() === 'id')
            if (idIndex !== -1) {
              updateKeyField.value = tableColumns.value[idIndex]
            } else {
              updateKeyField.value = tableColumns.value[0]
            }
          }
        } else {
          alert('JSON文件必须包含数组数据')
        }
      } catch (error) {
        console.error('JSON解析错误:', error)
        alert('JSON格式错误')
      }
    }
    reader.onerror = () => {
      alert('文件读取失败')
    }
    reader.readAsText(file)
  } catch (error) {
    console.error('文件处理错误:', error)
    alert('文件读取失败，请重试')
  }
}

// 生成SQL语句
const generateSQL = () => {
  let sql = ''

  // 生成建表语句
  if (generateCreateTable.value) {
    sql += `CREATE TABLE ${tableName.value} (\n`
    sql += selectedColumns.value
      .map((selected, index) => {
        if (!selected) return null
        const fieldName = sqlFields.value[index]
        const fieldType = fieldTypes.value[index]
        let typeStr = ''
        switch (fieldType) {
          case 'varchar':
            typeStr = 'VARCHAR(255)'
            break
          case 'decimal':
            typeStr = 'DECIMAL(10,2)'
            break
          default:
            typeStr = fieldType.toUpperCase()
        }
        return `  ${fieldName} ${typeStr}`
      })
      .filter(Boolean)
      .join(',\n')
    sql += '\n);\n\n'
  }

  // 生成数据操作语句
  const selectedData = previewData.value.map((row) => {
    const values = {}
    selectedColumns.value.forEach((selected, index) => {
      if (selected) {
        values[sqlFields.value[index]] = row[jsonFields.value[index]]
      }
    })
    return values
  })

  switch (sqlType.value) {
    case 'batch_insert':
      // 批量插入
      for (let i = 0; i < selectedData.length; i += batchSize.value) {
        const batch = selectedData.slice(i, i + batchSize.value)
        sql += generateInsertStatement(batch)
      }
      break
    case 'single_insert':
      // 单行插入
      selectedData.forEach((row) => {
        sql += generateInsertStatement([row])
      })
      break
    case 'update':
      // 更新语句
      selectedData.forEach((row) => {
        sql += generateUpdateStatement(row)
      })
      break
  }

  return sql
}

// 生成INSERT语句
const generateInsertStatement = (rows) => {
  if (rows.length === 0) return ''

  const columns = Object.keys(rows[0])
  const values = rows
    .map((row) => {
      return (
        '(' +
        columns
          .map((col) => {
            const value = row[col]
            if (value === null || value === undefined) return 'NULL'
            // 如果是数字字符串且长度超过15（可能是大数字），保持字符串格式
            if (typeof value === 'string' && /^\d{15,}$/.test(value)) {
              return `'${value}'`
            }
            if (typeof value === 'number') return value
            return `'${value.toString().replace(/'/g, "''")}'`
          })
          .join(', ') +
        ')'
      )
    })
    .join(',\n')

  return `INSERT INTO ${tableName.value} (\`${columns.join('`, `')}\`)\nVALUES\n${values};\n\n`
}

// 生成UPDATE语句
const generateUpdateStatement = (row) => {
  if (!updateKeyField.value) return ''

  const setColumns = Object.keys(row).filter((col) => col !== updateKeyField.value)
  const setClauses = setColumns
    .map((col) => {
      const value = row[col]
      const valueStr =
        value === null || value === undefined
          ? 'NULL'
          : typeof value === 'string' && /^\d{15,}$/.test(value)
            ? `'${value}'`
            : typeof value === 'number'
              ? value
              : `'${value.toString().replace(/'/g, "''")}'`
      return `  ${col} = ${valueStr}`
    })
    .join(',\n')

  const whereValue = row[updateKeyField.value]
  const whereValueStr =
    whereValue === null || whereValue === undefined
      ? 'NULL'
      : typeof whereValue === 'string' && /^\d{15,}$/.test(whereValue)
        ? `'${whereValue}'`
        : typeof whereValue === 'number'
          ? whereValue
          : `'${whereValue.toString().replace(/'/g, "''")}'`

  return `UPDATE ${tableName.value}\nSET\n${setClauses}\nWHERE ${updateKeyField.value} = ${whereValueStr};\n\n`
}

// 下载SQL文件
const handleDownload = () => {
  const sql = generateSQL()
  const blob = new Blob([sql], { type: 'text/plain' })
  const url = URL.createObjectURL(blob)
  const a = document.createElement('a')
  a.href = url
  a.download = `${tableName.value}.sql`
  document.body.appendChild(a)
  a.click()
  document.body.removeChild(a)
  URL.revokeObjectURL(url)
}

// 复制到剪贴板
const handleCopy = async () => {
  const sql = generateSQL()
  try {
    await navigator.clipboard.writeText(sql)
    alert('已复制到剪贴板')
  } catch (err) {
    console.log(err)
    alert('复制失败，请手动复制')
  }
}

// 全选/全不选功能
const selectAll = () => {
  selectedColumns.value = new Array(tableColumns.value.length).fill(true)
}

const unselectAll = () => {
  selectedColumns.value = new Array(tableColumns.value.length).fill(false)
}

// 处理拖拽进入
const handleDragOver = (event) => {
  event.target.closest('.file-upload').classList.add('dragover')
}

// 处理拖拽离开
const handleDragLeave = (event) => {
  event.target.closest('.file-upload').classList.remove('dragover')
}

// 处理文件拖放
const handleDrop = (event) => {
  event.target.closest('.file-upload').classList.remove('dragover')
  const file = event.dataTransfer.files[0]

  if (!file) return

  if (!file.type.includes('json')) {
    alert('只能上传 JSON 文件!')
    return
  }

  // 创建一个新的 FileList 对象
  const dataTransfer = new DataTransfer()
  dataTransfer.items.add(file)

  // 更新 input 的文件
  const fileInput = document.getElementById('jsonFile')
  fileInput.files = dataTransfer.files

  // 触发文件处理
  handleFileChange({ target: { files: [file] } })
}
</script>

<style scoped>
.json-to-sql-container {
  width: 100%;
  height: 100vh;
  overflow-y: auto;
  background-color: #f5f7f8;
  position: absolute;
  top: 0;
  left: 0;
  right: 0;
  bottom: 0;
}

.json-to-sql {
  padding: 20px;
  max-width: 1200px;
  margin: 0 auto;
  font-family:
    -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
}

.card {
  background: white;
  border-radius: 12px;
  box-shadow: 0 4px 24px rgba(0, 0, 0, 0.06);
  padding: 24px;
  margin-bottom: 20px;
}

.card-header {
  border-bottom: 1px solid #edf2f7;
  padding-bottom: 20px;
  margin-bottom: 24px;
}

.card-header h2 {
  margin: 0;
  font-size: 20px;
  color: #2d3748;
  font-weight: 600;
}

.file-upload {
  position: relative;
  display: block;
  padding: 30px;
  border: 2px dashed #e2e8f0;
  border-radius: 8px;
  background: #f8fafc;
  transition: all 0.3s ease;
  text-align: center;
  width: 100%;
  margin: 0 auto;
}

.file-upload.dragover {
  border-color: #4f46e5;
  background: #eef2ff;
}

.drag-tip {
  margin-top: 10px;
  color: #64748b;
  font-size: 14px;
}

.file-input {
  position: absolute;
  width: 0;
  height: 0;
  opacity: 0;
}

.upload-button {
  display: inline-flex;
  align-items: center;
  justify-content: center;
  padding: 15px 30px;
  background: #4f46e5;
  color: white;
  border-radius: 8px;
  cursor: pointer;
  transition: all 0.3s ease;
  font-weight: 500;
  width: 80%;
  max-width: 400px;
  font-size: 16px;
}

.upload-button:hover {
  background: #4338ca;
  transform: translateY(-1px);
  box-shadow: 0 4px 12px rgba(79, 70, 229, 0.2);
}

.upload-tip {
  margin-top: 15px;
  color: #64748b;
  font-size: 14px;
}

.upload-section {
  width: 100%;
  margin: 20px 0;
}

.preview-section {
  margin-top: 32px;
}

.preview-section h3,
.mapping-section h3 {
  font-size: 16px;
  color: #2d3748;
  margin-bottom: 16px;
  font-weight: 600;
}

.table-wrapper {
  overflow-x: auto;
  margin-top: 16px;
  border-radius: 8px;
  border: 1px solid #e2e8f0;
}

.preview-table {
  width: 100%;
  border-collapse: separate;
  border-spacing: 0;
  text-align: left;
}

.preview-table th,
.preview-table td {
  padding: 12px 16px;
  border-bottom: 1px solid #e2e8f0;
  font-size: 14px;
}

.preview-table th {
  background: #f8fafc;
  font-weight: 600;
  color: #475569;
  white-space: nowrap;
}

.preview-table tr:last-child td {
  border-bottom: none;
}

.preview-table tr:nth-child(even) {
  background: #f8fafc;
}

.preview-table tr:hover {
  background: #f1f5f9;
}

.mapping-section {
  margin: 32px 0;
}

.mapping-header {
  display: flex;
  align-items: center;
  gap: 24px;
  margin-bottom: 16px;
}

.mapping-actions {
  margin-left: auto;
  display: flex;
  gap: 12px;
}

.action-btn {
  padding: 8px 16px;
  border: 1px solid #e2e8f0;
  border-radius: 6px;
  cursor: pointer;
  transition: all 0.3s ease;
  font-size: 14px;
  font-weight: 500;
  background: white;
  color: #475569;
}

.action-btn.select {
  background: #4f46e5;
  color: white;
  border: none;
}

.action-btn.select:hover {
  background: #4338ca;
  transform: translateY(-1px);
  box-shadow: 0 4px 12px rgba(79, 70, 229, 0.2);
}

.action-btn.unselect {
  background: #dc2626;
  color: white;
  border: none;
}

.action-btn.unselect:hover {
  background: #b91c1c;
  transform: translateY(-1px);
  box-shadow: 0 4px 12px rgba(220, 38, 38, 0.2);
}

.text-input.readonly {
  background-color: #f8fafc;
  cursor: not-allowed;
  color: #64748b;
}

.text-input.readonly:hover,
.text-input.readonly:focus {
  border-color: #e2e8f0;
  box-shadow: none;
}

.mapping-list {
  display: flex;
  flex-direction: column;
  gap: 16px;
}

.mapping-item {
  padding: 16px;
  border: 1px solid #e2e8f0;
  border-radius: 8px;
  background: #f8fafc;
  transition: all 0.3s ease;
}

.mapping-item:hover {
  border-color: #cbd5e1;
  box-shadow: 0 2px 12px rgba(0, 0, 0, 0.04);
}

.mapping-content {
  display: flex;
  align-items: center;
  gap: 20px;
  margin-left: 12px;
}

.mapping-field {
  display: flex;
  align-items: center;
  gap: 10px;
}

.mapping-arrow {
  color: #94a3b8;
  margin: 0 12px;
  font-size: 18px;
}

.label {
  white-space: nowrap;
  color: #475569;
  font-size: 14px;
  font-weight: 500;
}

.text-input {
  padding: 8px 12px;
  border: 1px solid #e2e8f0;
  border-radius: 6px;
  width: 160px;
  font-size: 14px;
  transition: all 0.3s ease;
}

.text-input:hover {
  border-color: #cbd5e1;
}

.text-input:focus {
  border-color: #4f46e5;
  outline: none;
  box-shadow: 0 0 0 3px rgba(79, 70, 229, 0.1);
}

.select-input {
  padding: 8px 12px;
  border: 1px solid #e2e8f0;
  border-radius: 6px;
  width: 160px;
  background: white;
  font-size: 14px;
  cursor: pointer;
  transition: all 0.3s ease;
}

.select-input:hover {
  border-color: #cbd5e1;
}

.select-input:focus {
  border-color: #4f46e5;
  outline: none;
  box-shadow: 0 0 0 3px rgba(79, 70, 229, 0.1);
}

.download-section {
  margin: 32px 0;
  padding: 20px;
  border: 1px solid #e2e8f0;
  border-radius: 8px;
  background: #f8fafc;
}

.config-group {
  display: flex;
  flex-wrap: wrap;
  gap: 24px;
  margin-bottom: 24px;
}

.config-item {
  display: flex;
  align-items: center;
  gap: 10px;
}

.config-label {
  white-space: nowrap;
  color: #475569;
  font-size: 14px;
  font-weight: 500;
}

.checkbox-wrapper {
  display: flex;
  align-items: center;
  gap: 8px;
  cursor: pointer;
  user-select: none;
}

.checkbox-wrapper input[type='checkbox'] {
  width: 16px;
  height: 16px;
  margin: 0;
  cursor: pointer;
}

.checkbox-wrapper span {
  font-size: 14px;
  color: #475569;
}

.download-buttons {
  display: flex;
  gap: 12px;
  justify-content: flex-start;
  margin-top: 0;
  padding-top: 0;
  border-top: none;
}

.download-btn,
.copy-btn {
  display: inline-flex;
  align-items: center;
  justify-content: center;
  padding: 10px 24px;
  border: none;
  border-radius: 8px;
  cursor: pointer;
  transition: all 0.3s ease;
  font-size: 14px;
  font-weight: 500;
  min-width: 120px;
}

.download-btn {
  background: #4f46e5;
  color: white;
}

.download-btn:hover {
  background: #4338ca;
  transform: translateY(-1px);
  box-shadow: 0 4px 12px rgba(79, 70, 229, 0.2);
}

.copy-btn {
  background: white;
  color: #475569;
  border: 1px solid #e2e8f0;
}

.copy-btn:hover {
  background: #f8fafc;
  border-color: #cbd5e1;
  transform: translateY(-1px);
  box-shadow: 0 4px 12px rgba(0, 0, 0, 0.05);
}

@media (max-width: 768px) {
  .mapping-content {
    flex-direction: column;
    align-items: flex-start;
    gap: 12px;
  }

  .mapping-arrow {
    display: none;
  }

  .config-group {
    flex-direction: column;
    gap: 16px;
  }
}
</style>
